# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import mysql.connector
import csv


class MySQLPipeline:
    def open_spider(self, spider):
        try:
            # 连接 MySQL 数据库
            self.conn = mysql.connector.connect(
                host='localhost',  # 数据库主机
                user='root',  # 用户名
                password='Wlj98192188?',  # 密码，替换为你自己的密码
                database='stock_data'  # 数据库名
            )
            self.cursor = self.conn.cursor()
            print("MySQL connection established")
        except mysql.connector.Error as err:
            print(f"Error: {err}")
            raise

    def close_spider(self, spider):
        try:
            # 在关闭数据库连接前导出数据到 CSV 文件
            self.export_to_csv()

            # 提交数据并关闭数据库连接
            if hasattr(self, 'conn'):
                self.conn.commit()
                self.cursor.close()
                self.conn.close()
                print("MySQL connection closed")
        except AttributeError:
            print("No database connection to close.")
        except mysql.connector.Error as err:
            print(f"Error closing MySQL connection: {err}")

    def process_item(self, item, spider):
        try:
            # 插入数据到 MySQL
            insert_query = """
            INSERT INTO stocks (bStockNo, bStockName, bLatestPrice, bChangePercent, bChangeAmount, bVolume, bAmplitude, bHigh, bLow, bOpen, bPreviousClose)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            values = (
                item['bStockNo'],
                item['bStockName'],
                item['bLatestPrice'],
                item['bChangePercent'],
                item['bChangeAmount'],
                item['bVolume'],
                item['bAmplitude'],
                item['bHigh'],
                item['bLow'],
                item['bOpen'],
                item['bPreviousClose']
            )
            self.cursor.execute(insert_query, values)
            self.conn.commit()  # Commit after each insert
            return item
        except mysql.connector.Error as err:
            print(f"Error inserting data: {err}")
            return None

    def export_to_csv(self):
        # 从数据库中查询所有数据并导出到 CSV 文件
        export_query = "SELECT * FROM stocks"
        try:
            self.cursor.execute(export_query)
            rows = self.cursor.fetchall()
            headers = [i[0] for i in self.cursor.description]  # 获取表头

            with open('exported_stock_data.csv', mode='w', newline='', encoding='utf-8') as file:
                writer = csv.writer(file)
                writer.writerow(headers)  # 写入表头
                writer.writerows(rows)  # 写入数据行
            print("Data exported to exported_stock_data.csv")
        except mysql.connector.Error as err:
            print(f"Error exporting data: {err}")
